package com.ai.bitcoin.jpa.dao;

import com.ai.bitcoin.jpa.entity.BccUserPlcRecord;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface BccUserPlcRecordDao extends JpaRepository<BccUserPlcRecord, String>, JpaSpecificationExecutor<BccUserPlcRecord> {

    List<BccUserPlcRecord> findByOpenId(String openId);



    @Query(nativeQuery = true,
            value = "\n" +
                    "SELECT *  FROM (\n" +
                    "SELECT (@i\\:=@i+1) AS rank,base.*FROM (\n" +
                    "SELECT open_id AS openId,count(1) invitedNum FROM bcc_user_plc_record WHERE type=1 GROUP BY open_id ORDER BY invitedNum DESC) base,(SELECT @i\\:=0) AS it) temp WHERE openId=:openId")
    List<Object[]> queryRankAndInvitedNum(@Param("openId")  String openId);


    @Query(nativeQuery = true, value = "    SELECT  count(1) FROM (\n" +
            " SELECT  count(1) a  FROM bcc_user_plc_record WHERE type=1 GROUP BY open_id \n" +
            " ) temp")
    int countInvitedTotal();
}
